package com.kaifamiao.wendao.dao;

import com.kaifamiao.utils.JdbcHelper;
import com.kaifamiao.utils.RowHandler;
import com.kaifamiao.wendao.entity.Customer;
import com.kaifamiao.wendao.entity.Topic;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;

public class TopicDao implements Dao<Topic,Integer>{

    private RowHandler<Topic> topicRowHandler = new RowHandler<Topic>() {
        @Override
        public Topic handle(ResultSet rs) throws SQLException {
            Topic t = new Topic();
            t.setId( rs.getInt( "id" ) );
            t.setTitle( rs.getString( "title" ) );
            t.setContent( rs.getString( "content" ) );
            t.setPublishIp( rs.getString( "publish_ip" ) );

            Timestamp ts = rs.getTimestamp( "publish_time" );
            if( ts != null ) {
                LocalDateTime publishTime = ts.toLocalDateTime();
                t.setPublishTime( publishTime );
            }
            // 获得发布者的编号
            Integer cid = rs.getInt( "cid" );
            // 创建一个 Customer 实例 (当作容器来使用)
            Customer p = new Customer();
            // 将 发布者的编号 设置到 Customer 对象中
            p.setId( cid );
            // 再将 Customer 对象设置到 Topic
            t.setPublisher( p );
            return t;
        }
    };

    @Override
    public Topic find(Integer id) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , title , content , publish_time , publish_ip , cid " +
                     "FROM t_topics " +
                     "WHERE id = ? " ;
        Topic t = helper.single( sql , topicRowHandler , id );
        helper.release();
        return t;
    }

    @Override
    public List<Topic> findAll() {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , title , content , publish_time , publish_ip , cid " +
                     "FROM t_topics " +
                     "ORDER BY publish_time DESC " ;
        List<Topic> list = helper.query( sql , topicRowHandler );
        helper.release();
        return list;
    }

    /**
     * 分页查询
     * @param start 从第几行开始获取数据
     * @param size 每页最多包含的记录数目
     * @return
     */
    public List<Topic> paging( int start , int size ){
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , title , content , publish_time , publish_ip , cid " +
                     "FROM t_topics " +
                     "ORDER BY publish_time DESC " +
                     "LIMIT ? , ? " ;
        List<Topic> list = helper.query( sql , topicRowHandler , start , size );
        helper.release();;
        return list;
    }

    public Integer count(){
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT count(*) FROM t_topics" ;
        Integer rowCount = helper.single(sql, new RowHandler<Integer>() {
            @Override
            public Integer handle(ResultSet rs) throws SQLException {
                return rs.getInt( 1 );
            }
        });
        helper.release();;
        return rowCount;
    }

    public Integer countMine( int customerId ){
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT count(*) FROM t_topics WHERE cid = ? " ;
        Integer rowCount = helper.single(sql, new RowHandler<Integer>() {
            @Override
            public Integer handle(ResultSet rs) throws SQLException {
                return rs.getInt( 1 );
            }
        }, customerId );
        helper.release();;
        return rowCount;
    }

    /**
     * 根据 t_customer 的 主键值 查询话题列表
     * @param cid 用作查询条件的 t_customer 主键值
     * @return 返回指定的 t_customer 主键值 所对应的所有话题
     */
    public List<Topic> findMine( int cid , int start , int pageSize){
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , title , content , publish_time , publish_ip , cid " +
                     "FROM t_topics " +
                     "WHERE cid = ? " +
                     "ORDER BY publish_time DESC " +
                     "LIMIT ? , ? " ;
        List<Topic> list = helper.query( sql , topicRowHandler , cid , start , pageSize );
        helper.release();
        return list;
    }

    /**
     * 查询数据库返回新的数据库主键值
     * @return
     */
    public Integer nextId(){
        JdbcHelper helper = JdbcHelper.getInstance();
        // MySQL
        String sql = "SELECT IFNULL( max( id ) , 0 ) + 1 FROM t_topics" ;
        // Oracle
        // String sql = "SELECT NVL( max( id ) , 0 ) + 1 FROM t_topics" ;
        Integer id = helper.single(sql, new RowHandler<Integer>() {
            @Override
            public Integer handle(ResultSet rs) throws SQLException {
                return rs.getInt( 1 );
            }
        });
        helper.release();
        return id ;
    }

    @Override
    public boolean save(Topic t) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "INSERT INTO t_topics " +
                     "( id , title , content , publish_time , publish_ip , cid ) " +
                     "VALUES ( ? , ? , ? , ? , ? , ? )" ;
        Timestamp publishTime = Timestamp.valueOf( t.getPublishTime() );
        Integer customerId = null ;
        // 获取发布者
        Customer publisher = t.getPublisher() ;
        // 得到发布者的 ID
        customerId = publisher != null ? publisher.getId() : null ;
        Object[] params = { t.getId() , t.getTitle() , t.getContent() , publishTime , t.getPublishIp() , customerId };
        int count = helper.execute( sql , params );
        helper.release();

        return count == 1 ;
    }

    @Override
    public boolean update(Topic topic) {
        return false;
    }

    @Override
    public boolean remove(Integer id) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "DELETE FROM t_topics WHERE id = ? " ;
        int count = helper.execute( sql , id );
        helper.release();
        return count == 1 ;
    }

}
